﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FIPSSearch
{
    class CensusRepository : RepositoryBase
    {
        public CensusRepository(string connectionString) : base(connectionString) { }


        /// <summary>
        /// Creates the County table from input file
        /// </summary>
        /// <param name="inputfile">file of counties</param>
        /// <returns>County Table</returns>
        public List<County> ParseCountyFile(FileInfo inputfile)
        {
            var result = new List<County>();

            using (var file = inputfile.OpenText())
            {
                var headerLine = file.ReadLine();
                while (!file.EndOfStream)
                {
                    var line = file.ReadLine();
                    var fields = line.Split(',');

                    var county = new County();

                    county.State = fields[0];
                    county.StateCode = short.Parse(fields[1]);
                    county.CountyCode = short.Parse(fields[2]);
                    county.CountyName = fields[3];
                    county.ClassificationCode = fields[4];

                    result.Add(county);
                }
            }

            return result;
        }

        /// <summary>
        /// Clears the County table using SQL command
        /// </summary>
        public void ClearTable()
        {
            base.ClearTable("dbo.County");
        }

        /// <summary>
        /// Loads the County table using SQL Bulk Copy
        /// </summary>
        /// <param name="source">list of counties to upload</param>
        public void LoadTableFast(IList<County> source)
        {
            var sw = new Stopwatch();
            sw.Start();

            var table = new DataTable();
            table.Columns.Add("State", typeof(string));
            table.Columns.Add("StateCode", typeof(short));
            table.Columns.Add("CountyCode", typeof(short));
            table.Columns.Add("CountyName", typeof(string));
            table.Columns.Add("ClassificationCode", typeof(string));

            foreach (var item in source)
            {
                var row = table.NewRow();
                row["State"] = item.State;
                row["StateCode"] = item.StateCode;
                row["CountyCode"] = item.CountyCode;
                row["CountyName"] = item.CountyName;
                row["ClassificationCode"] = item.ClassificationCode;
                table.Rows.Add(row);
            }


            UploadDataTable(table, "dbo.County");

            sw.Stop();
            Console.WriteLine("Using ADO with Bulk Copy took " + sw.Elapsed.TotalSeconds + " seconds");

        }

        /// <summary>
        /// Loads the County table using Entity Framework
        /// </summary>
        /// <param name="source">list of counties to upload</param>
        public void LoadTable(IList<County> source)
        {
            Console.WriteLine("Starting to load county with EF");
            var sw = new Stopwatch();
            sw.Start();

            using (var context = new CensusToolsEntities())
            {
                context.Configuration.AutoDetectChangesEnabled = false; 

                foreach (var county in source)
                {
                    context.Counties.Add(county);
                }

                context.SaveChanges();
            }

            sw.Stop();
            Console.WriteLine("Using EF took " + sw.Elapsed.TotalSeconds + " seconds");
        }
    }
}
